// $codepro.audit.disable packageNamingConvention

/** Packages */
package edu.gatech.team404.dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.gatech.team404.Account;
import edu.gatech.team404.AccountType;
import edu.gatech.team404.Course;
import edu.gatech.team404.Lab;
import edu.gatech.team404.tool.Log;

/**
 * A Data Access Layer for CourseTable and StudentTable.
 * @author Michael Dandy
 * @version 1.0
 */
public class CourseDAL extends IDatabase {
	/** Class tag for logging purpose */
	private final String TAG = "CourseDAL";
	
	/** The Course table that this DAL is associated with */
	private final String COURSE_TABLE = "CourseTable";
	
	/** The Student table that this DAL is associated with */
	private final String STUDENT_TABLE = "StudentTable";
	
	/**
	 * Create course.
	 * @param course the course to be created
	 * @return true on success or false otherwise
	 */
	public boolean createCourse(Course course) {
		final String query = "INSERT INTO " + COURSE_TABLE + " VALUES (" 
				+ "\'" + course.getName() + "\', " 
				+ "\'" + course.getDescription() + "\', " 
				+ "\'" + course.getInstructor() + "\')";
		Log.info(TAG, "Adding course ...");
		Log.info(TAG, query);
		return super.executeUpdate(query);
	}
	
	/**
	 * Get a course with the specified name.
	 * @param name the name of the course
	 * @return course of the specified name
	 */
	public Course getCourseByName (String name) {
		final String query = "SELECT * FROM " + COURSE_TABLE 
							+ " WHERE Name = \'" + name + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving course by name ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				if (result.next()) {
					final Course course = new Course (result.getString(1), 
													  result.getString(2), 
													  result.getString(3));
					return course;
				}
			} 
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Get all courses taught by the specified instructor.
	 * @param instructorUsername the username of the instructor
	 * @return list of courses taught by the specified instructor
	 */
	public List<Course> getCoursesByInstructor (String instructorUsername) {
		final String query = "SELECT * FROM " + COURSE_TABLE
							+ " WHERE Instructor = \'" + instructorUsername + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving course by instructor ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				final List<Course> courses = new ArrayList<Course>();
				while(result.next()) {
					courses.add(new Course (result.getString(1), result.getString(2), 
										   result.getString(3)));
				}
				return courses;
			}
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Get all courses taken by the specified student.
	 * @param studentUsername the username of the student
	 * @return list of courses taken by the specified student
	 */
	public List<Course> getCoursesByStudent (String studentUsername) {
		final String query = "SELECT Course FROM " + STUDENT_TABLE
							+ " WHERE Username = \'" + studentUsername + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving course by student ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				final List<Course> courses = new ArrayList<Course>();
				while(result.next()) {
					courses.add(getCourseByName(result.getString(1)));
				}
				return courses;
			}
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Get all courses in the system.
	 * @return all courses in the system
	 */
	public List<Course> getAllCourses () {
		final String query = "SELECT * FROM " + COURSE_TABLE;
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving all courses ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				final List<Course> courses = new ArrayList<Course>();
				while(result.next()) {
					courses.add(new Course (result.getString(1), result.getString(2), 
										   result.getString(3)));
				}
				return courses;
			}
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Update a course. Only description and instructor information can
	 * be updated. The name of the course serves as lookup ID.
	 * @param course the course to be updated
	 * @return true on success or false otherwise
	 */
	public boolean updateCourse (Course course) {
		String query = "UPDATE " + COURSE_TABLE + " SET ";
		String update = "";
		final Course old = this.getCourseByName(course.getName());
		
		if (!course.getDescription().isEmpty() && 
			!old.getDescription().equalsIgnoreCase(course.getDescription())) {
			update += "Description=\'" + course.getDescription() + "\'";
		}
		if (!course.getInstructor().isEmpty() && 
			!old.getInstructor().equalsIgnoreCase(course.getInstructor())) {
			if (!update.isEmpty()) {
				update += ", ";
			}
			update += "Instructor=\'" + course.getInstructor() + "\'";
		}
		
		query += update;
		query += " WHERE Name=\'" + course.getName() + "\'";
		
		Log.info(TAG, "Updating course ...");
		Log.info(TAG, query);
		return super.executeUpdate(query);
	}
	
	/**
	 * Delete a course from the system.
	 * @param name the name of the course to be deleted
	 * @return true on success or false otherwise
	 */
	public boolean deleteCourse (String name) {
		boolean ret = true;
		
		LabDAL dal = new LabDAL();
		List<Lab> labs = dal.getAllLabsInCourse(name);
		
		String query = "DELETE FROM " + COURSE_TABLE 
							+ " WHERE Name=\'" + name + "\'";	
		Log.info(TAG, "Removing course ...");
		Log.info(TAG, query);
		ret &= super.executeUpdate(query);
		
		query = "DELETE FROM " + STUDENT_TABLE 
				+ " WHERE Course=\'" + name + "\'";	
		Log.info(TAG, "Removing student from course ...");
		Log.info(TAG, query);
		ret &= super.executeUpdate(query);
		
		for (Lab lab : labs) {
			ret &= dal.deleteLab(lab.getId());
		}
		
		return ret;
	}
	
	/**
	 * Sign a student up for a course. 
	 * @param username the username of a student
	 * @param course the course that the student signs up for
	 * @return true on success or false otherwise
	 */
	public boolean signUpForCourse (String username, Course course) {
		//pull student information from AccountTable
		final AccountDAL aDal = new AccountDAL();
		final Account student = aDal.getAccount(username);
		
		if (null != student) {
			final String query = "INSERT INTO " + STUDENT_TABLE + " VALUES (" 
									+ "\'" + student.getUsername() + "\', " 
									+ "\'" + student.getName() + "\', " 
									+ "\'" + course.getName() + "\')";
			Log.info(TAG, "Sign up for course ...");
			Log.info(TAG, query);
			return super.executeUpdate(query);
		}
		return false;
	}
	
	/**
	 * Get all the students sign up for the specified course.
	 * @param courseName the name of the course 
	 * @return all the students sign up for the course
	 */
	public List<Account> getStudents(String courseName) {
		final String query = "SELECT Username FROM " + STUDENT_TABLE
							+ " WHERE Course  = \'" + courseName + "\'";
		final ResultSet result = super.executeQuery(query);
		
		Log.info(TAG, "Retrieving student sign up for a course ...");
		Log.info(TAG, query);
		
		if (result != null) {
			try {
				final List<Account> accts = new ArrayList<Account>();
				final AccountDAL acctDal = new AccountDAL();
				while(result.next()) {
					Account acct = acctDal.getAccount(result.getString(1));
					if (acct.getType() == AccountType.STUDENT) {
						accts.add(acct);
					}
				}
				return accts;
			}
			catch (SQLException e) {
				Log.error(TAG, e);
				return null;
			}
		}
		return null;
	}
	
	/**
	 * Close SQL connection
	 * @return true on success or false otherwise
	 */
	public boolean close() {
		return super.close();
	}
}
